
package ru.creator.logical.links.db;


import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import ru.creator.logical.links.сore.util.Log;




/**
 *
 * @author ~jo-MA-jo~
 */
public class DB extends SQLite{
    private Log logger;
    
    
    public DB(Log logger)throws Exception {
       this.logger = logger;
    }

    public Table getObject(Table t, String q){
        try{
            t=t.getTableObject(getDb(), q);
        } catch (Exception ex){
            logger.err(ex.getMessage());
        }
        return t;
    }



    public List<Table> getObjectList(Table t, String q){
        List<Table> list = new ArrayList<Table>();
        try{
            list=t.getTableList(getDb(), q);
        } catch (Exception ex){
            ex.printStackTrace();
            logger.err(ex.getMessage());
        } 
        return list;
    }


//    public void insertObject(Table t){
//        try{
//            PreparedStatement pst = t.insertPrepStat(getDb());
//            pst.execute();
//            pst.close();
//        } catch (Exception ex){
//            ex.printStackTrace();
//        }
//    }

    public void insertObject(Table t){
        try{
             PreparedStatement pst = null;
             LinkedHashMap<String, TableObject> structure = t.getStructure();
             String sql = "insert into "+t.getTableName()+" values (";
             for(int i=0; i< structure.size(); i++){
                sql+= (i!=structure.size()) ? "?" : "";
                sql+= (i+1<structure.size()) ? "," : ")";
             }
            pst = getDb().prepareStatement(sql);
            int i=0;
            for(String field : structure.keySet()){
                 i++;
                     TableObject to = structure.get(field);
                     switch (to.type){
                       case Types.INTEGER:
                           pst.setInt(i,(Integer)to.content);
                           continue;
                       case Types.VARCHAR:
                           pst.setString(i,(String)to.content);
                           continue;
                       case Types.TIMESTAMP:
                           pst.setTimestamp(i,new Timestamp((Long)to.content));
                           continue;
                       case Types.JAVA_OBJECT:
                           pst.setBytes(i, to.contentByte);
                           continue;    
                    }

             }
            pst.execute();
            pst.close();
        } catch (Exception ex){
            ex.printStackTrace();
            logger.err(ex.getMessage());
        }
    }


//      public void updateObject(Table o){
//        try{
//            PreparedStatement pst = o.updatePrepStat(getDb());
//            pst.execute();
//            pst.close();
//        } catch (Exception ex){
//            ex.printStackTrace();
//        }
//    }

     public void updateObject(Table t){
        try{
             PreparedStatement pst = null;
             LinkedHashMap<String, TableObject> structure = t.getStructure();
             String sql = "update "+t.getTableName()+" set ";

             int i=0;
            for(String field : structure.keySet()){
                i++;
                if (!"id".equalsIgnoreCase(field)){
                sql+= (i!=structure.size()) ? field+"=?" : field+"=? where id=?";//+t.getId();
                sql+= (i+1<=structure.size()) ? "," : "";
                }
             }
             pst = getDb().prepareStatement(sql);
            i=0;
            int id=0;
            for(String field : structure.keySet()){
                     //id = (i==structure.size()) ? 1 : id++;
                     i = ("id".equalsIgnoreCase(field)) ? structure.size() : ((i==structure.size()) ? 1 : id++);
                     TableObject to = structure.get(field);
                     switch (to.type){
                       case Types.INTEGER:
                           pst.setInt(i,(Integer)to.content);
                           continue;
                       case Types.VARCHAR:
                           pst.setString(i,(String)to.content);
                           continue;
                       case Types.TIMESTAMP:
                           pst.setTimestamp(i,new Timestamp((Long)to.content));
                           continue;
                       case Types.JAVA_OBJECT:
                           pst.setBytes(i, to.contentByte);
                           continue;                               
                    }
             }
            pst.execute();
            pst.close();
        } catch (Exception ex){
            ex.printStackTrace();
            logger.err(ex.getMessage());
        }
    }


      public void createDB(String pathDb) {
         createDB(pathDb+"/words.db", "words.sql");
         createDB(pathDb+"/subjects.db", "subjects.sql");
         createDB(pathDb+"/links.db", "links.sql");
       }
    
      public void createDB(String filePath, String SQLdump) {
         File file = new File(filePath); 
         File fileSQL = new File(file.getParent()+"/"+SQLdump); 
         file.mkdirs();
        try {
                    if (!testFile(file)){
                     String SQL= "";    
                        //BufferedReader r = new BufferedReader(new InputStreamReader(new FileInputStream(fname), "windows-1251"));
                        BufferedReader r = (testFile(fileSQL)) ? 
                            new BufferedReader(new InputStreamReader
                                (new FileInputStream(fileSQL.getPath()), "windows-1251"))
                            :
                            new BufferedReader(new InputStreamReader
                                (this.getClass().getClassLoader().getResourceAsStream("ru/creator/logical/links/db/sql/"+SQLdump)));
                        while (r.ready()) SQL += r.readLine() + '\n';
                        r.close();    
                     Class.forName("org.sqlite.JDBC");   
                     Connection con=DriverManager.getConnection("jdbc:sqlite:"+file.getPath());
                     PreparedStatement Pstat = null;
                     String[] SQLrequest = SQL.split(";\n");
                       for(int i_sql=0; i_sql<SQLrequest.length; i_sql++){
                           try {
                                Pstat = (PreparedStatement)con.prepareStatement(SQLrequest[i_sql].replace("<br>", "\n"));
                                Pstat.execute();
                                Pstat.close();
                              //  System.out.println("SQL: "+name+".sql - str#"+(i_sql+1)+" ok!");
                               } catch (Exception e) { }
                       }
                      con.close();
                      con=null;
                      // System.out.println("Created new db: " + file.getName());
                       logger.db("Created new db: " + file.getName());
                    }
         } catch (Exception ex) {
            ex.printStackTrace();
            logger.err(ex.getMessage());
         }

    }

     /**
      * проверка наличия файла или дирректории
      * @param file
      * @return
      */
      public boolean testFile(File file) {
           if (file.length()==0)file.delete();
           return file.exists();
     }
      
                
              
              
}